<?php declare(strict_types=1);
/**
 * @author      xianganyall <xianganyall@gmail.com>
 * @copyright   2023-2025 owner
 **/

namespace Srv\Libs\Storage;

use mysqli as Mysqli;
use mysqli_sql_exception as MysqliSqlException;
use mysqli_driver as MysqliDriver;
use mysqli_result as MysqliResult;
use Srv\Libs\Common\CommJson;
use Srv\Libs\Common\CommTime;
use Srv\Libs\Common\CommValid;
use Srv\Libs\ConfModel\DbConf;
use Srv\Libs\Exception\MysqlConnectException;
use Srv\Libs\Frame\Conf;
use Srv\Libs\Frame\Logs;
use Srv\Libs\Pools\PoolDb;
use Srv\Libs\Pools\PoolItem;

class DataMysql extends DataStorageAbstract
{
    private ?PoolDb $PoolDb                 = null;                 // DB对象集合
    private ?PoolItem $PoolItem             = null;                 // DBItem单个
    private ?MysqliDriver $MysqliDriver     = null;                 // Mysqli驱动对象
    private ?Mysqli $Mysqli                 = null;                 // Mysqli对象
    private ?MysqliResult $MysqliResult     = null;                 // Query结果
    private bool $ssl                       = false;                // 是否SSL连接
    private string $serverUser              = '';                   // USER
    private string $serverPass              = '';                   // PASS
    private bool $hostIsSock                = false;                // HOST是否是Unix
    private string $serverHost              = '127.0.0.1';          // HOST
    private int $serverPort                 = 3306;                 // PORT
    private string $serverDbName            = '';                   // DataBase名称
    private string $serverCharset           = 'utf8mb4';            // DataBase编码
    private string $serverCollate           = 'utf8mb4_unicode_ci'; // DataBase编码字符集
    private string $tablePrefix             = '';                   // 表前缀
    private string $sqlMode                 = '';                   // SQL模式[NONE-为不设置][STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_AUTO_VALUE_ON_ZERO,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO]
    private bool $isKeepAlive               = false;                // 是否启用长连接
    private string $logPath                 = '';                   // 日志路径
    private string $persistentId            = '';                   // 连接名
    private bool $isTransaction             = false;                // 是否启用了事务
    private int $optConnectTimeOut          = 10;                   // 连接超时时长[秒]
    private int $optReadTimeOut             = 30;                   // SQL读取超时时长[秒]
    private int $optWriteTimeOut            = 30;                   // SQL写入超时时长[秒]

    /**
     * DataMysql constructor.
     * @param DbConf $DbConf
     * @param string $persistentId
     */
    public function __construct(DbConf $DbConf, string $persistentId)
    {
        $server                 = trim($DbConf->getHost());
        $port                   = $DbConf->getPort();
        if(CommValid::isUnix($server)){     // unix:///srv/conf/mysql/mysql.sock
            $server             = substr($server, 7);
            $port               = 0;
            $this->hostIsSock   = true;
        }else{
            $port               = min(max($port, 0), 65535);
            $this->hostIsSock   = false;
        }
        $this->serverHost       = $server;
        $this->serverPort       = $port;
        $this->serverPass       = $DbConf->getPassword();
        $this->serverUser       = $DbConf->getUsername();
        $this->serverDbName     = $DbConf->getDbname();
        $this->serverCharset    = $DbConf->getCharset();
        $this->serverCollate    = $DbConf->getCollate();
        $this->tablePrefix      = $DbConf->getPrefix();
        $this->isKeepAlive      = $DbConf->isKeepalive();
        $this->sqlMode          = $DbConf->getSqlMode();
        $this->logPath          = $DbConf->getLogPath();
        $this->ssl              = $DbConf->isSsl();
        $this->persistentId     = $persistentId;
    }

    /**
     * @param string $groupName
     * @param string $persistentId
     * @param string $tagName
     * @return static
     * tag
     */
    final public static function tag(string $groupName, string $persistentId, string $tagName = 'auto'):self
    {
        $thisObject             = static::tagGet($tagName, [Conf::getDbConfGroup()->getByGroupName($groupName), $persistentId]);
        $thisObject->connect();
        return $thisObject;
    }

    /**
     * __destruct
     */
    public function __destruct()
    {
        $this->close();
    }

    /**
     * @return bool
     * isUnixHost
     */
    public function isUnixHost():bool
    {
        return $this->hostIsSock;
    }

    /**
     * @param PoolDb $PoolDb
     * @param PoolItem $PoolItem
     * @return bool
     * 初始化连接池
     */
    public function initPool(PoolDb $PoolDb, PoolItem $PoolItem):bool
    {
        if($this->PoolDb instanceof PoolDb || $this->PoolItem instanceof PoolItem) return false;
        $this->PoolDb       = $PoolDb;
        $this->PoolItem     = $PoolItem;
        return true;
    }

    /**
     * @return bool
     * 释放连接池
     */
    public function freePool():bool
    {
        return $this->PoolDb instanceof PoolDb && $this->PoolItem instanceof PoolItem && $this->PoolDb->freePoolItem($this->PoolItem);
    }

    /**
     * @return bool
     * 是否是在使用
     */
    public function isUse():bool
    {
        return ($this->PoolDb instanceof PoolDb && $this->PoolItem instanceof PoolItem && $this->PoolItem->isUse());
    }

    /**
     * @return bool
     * 是否已连接
     */
    public function isConnected():bool
    {
        if(($this->Mysqli instanceof Mysqli) && $this->Mysqli->thread_id > 0){
            try{
                return $this->Mysqli->ping();
            }catch(MysqliSqlException $MysqliSqlException){
                return false;
            }
        }else{
            return false;
        }
    }

    /**
     * @throws MysqlConnectException
     * 开启连接
     */
    public function connect():bool
    {
        if(strlen($this->serverUser) < 1 || strlen($this->serverPass) < 1 || strlen($this->serverHost) < 1 || false === preg_match('/^[a-z\d_]$/i', $this->serverDbName) || false === preg_match('/^[a-z\d_]$/i', $this->serverCharset) || false === preg_match('/^[a-z\d_]$/i', $this->serverCollate)){
            throw new MysqlConnectException(CommJson::encodeArray(['msg' => 'Configure error', 'host' => strlen($this->serverHost) > 0, 'pass' => strlen($this->serverPass) > 0, 'dbName' => strlen($this->serverDbName) > 0, 'charset' => $this->serverCharset, 'collate' => $this->serverCollate]), -1);
        }
        if($this->isConnected()) return true;
        if(!($this->MysqliDriver instanceof MysqliDriver)){
            $this->MysqliDriver                 = new MysqliDriver();
            $this->MysqliDriver->report_mode    = MYSQLI_REPORT_ALL & ~MYSQLI_REPORT_INDEX;
        }
        try{
            !defined('MYSQLI_OPT_CONNECT_TIMEOUT') && define('MYSQLI_OPT_CONNECT_TIMEOUT', 0);
            !defined('MYSQLI_OPT_READ_TIMEOUT') && define('MYSQLI_OPT_READ_TIMEOUT', 11);
            !defined('MYSQLI_OPT_WRITE_TIMEOUT') && define('MYSQLI_OPT_WRITE_TIMEOUT', 12);
            $this->Mysqli   = new Mysqli();
            if($this->optConnectTimeOut > 0) $this->Mysqli->options(MYSQLI_OPT_CONNECT_TIMEOUT, $this->optConnectTimeOut);
            if($this->optReadTimeOut > 0) $this->Mysqli->options(MYSQLI_OPT_READ_TIMEOUT, $this->optReadTimeOut);
            if($this->optWriteTimeOut > 0) $this->Mysqli->options(MYSQLI_OPT_WRITE_TIMEOUT, $this->optWriteTimeOut);
            if($this->hostIsSock){
                if($this->ssl){
                    $this->Mysqli->real_connect(($this->isKeepAlive ? 'p:' : '').'localhost', $this->serverUser, $this->serverPass, $this->serverDbName, $this->serverPort, $this->serverHost, MYSQLI_CLIENT_SSL);
                }else{
                    $this->Mysqli->real_connect(($this->isKeepAlive ? 'p:' : '').'localhost', $this->serverUser, $this->serverPass, $this->serverDbName, $this->serverPort, $this->serverHost);
                }
            }else{
                if($this->ssl){
                    $this->Mysqli->real_connect(($this->isKeepAlive ? 'p:' : '').$this->serverHost, $this->serverUser, $this->serverPass, $this->serverDbName, $this->serverPort, null, MYSQLI_CLIENT_SSL);
                }else{
                    $this->Mysqli->real_connect(($this->isKeepAlive ? 'p:' : '').$this->serverHost, $this->serverUser, $this->serverPass, $this->serverDbName, $this->serverPort);
                }
            }
        }catch(MysqliSqlException $MysqliSqlException){
            $this->Mysqli   = null;
            throw new MysqlConnectException(CommJson::encodeArray(['msg' => 'Connection failed, '.$MysqliSqlException->getMessage(), 'host' => strlen($this->serverHost) > 0, 'pass' => strlen($this->serverPass) > 0, 'dbName' => strlen($this->serverDbName) > 0, 'pId' => $this->persistentId]), -1);
        }
        if(!$this->Mysqli->set_charset($this->serverCharset)){
            throw new MysqlConnectException(CommJson::encodeArray(['msg' => 'SET CHARSET failed', 'charset' => $this->serverCharset]), -1);
        }
        if(!$this->queryInit('SET NAMES \''.$this->getRealEscapeString($this->serverCharset).'\' COLLATE \''.$this->getRealEscapeString($this->serverCollate).'\'')){
            throw new MysqlConnectException(CommJson::encodeArray(['msg' => 'SET NAMES failed', 'charset' => $this->serverCharset, 'collate' => $this->serverCollate]), -1);
        }
        if(!$this->queryInit('SET SQL_MODE=\''.$this->getRealEscapeString($this->sqlMode).'\'')){
            throw new MysqlConnectException(CommJson::encodeArray(['msg' => 'SET SQL_MODE failed', 'sqlMode' => $this->sqlMode]), -1);
        }
        $this->Mysqli->autocommit(true);
        return true;
    }

    /**
     * @return bool
     * 关闭连接且释放内存资源
     */
    public function close():bool
    {
        $this->free();
        if($this->Mysqli instanceof Mysqli) @$this->Mysqli->close();
        $this->Mysqli       = null;
        $this->MysqliDriver = null;
        return true;
    }

    /**
     * @return void
     * 释放内存资源
     */
    private function free():void
    {
        if($this->MysqliResult instanceof MysqliResult){
            $this->MysqliResult     = null;
        }
    }

    /**
     * @param string $sql
     * @return bool
     * 执行初始化QuerySQL
     */
    private function queryInit(string $sql):bool
    {
        try{
            $queryResult    = $this->Mysqli->query($sql, MYSQLI_STORE_RESULT);
            if($queryResult instanceof MysqliResult) $this->MysqliResult = $queryResult;
            return !!$queryResult;
        }catch(MysqliSqlException $MysqliSqlException){
        }
        return false;
    }

    /**
     * @param string $sql
     * @param bool $isNoLog
     * @param bool $isNoReConnect
     * @return bool
     * 执行QuerySQL
     */
    public function query(string $sql, bool $isNoLog = false, bool $isNoReConnect = false):bool
    {
        try{
            $queryStartTimeMicro    = CommTime::getTimeMicro();
            $queryResult            = $this->Mysqli->query($sql, MYSQLI_STORE_RESULT);
            $queryEndTimeMicro      = CommTime::getTimeMicro();
            if($queryResult instanceof MysqliResult) $this->MysqliResult = $queryResult;
            if($queryResult){
                if(!$isNoLog && strlen($this->logPath) > 0) Logs::debug('[Time:'.($queryEndTimeMicro - $queryStartTimeMicro).']->'.$sql, __LINE__, __FILE__, $this->logPath);
                return true;
            }
        }catch(MysqliSqlException $MysqliSqlException){}
        $isReconnect                = in_array($this->Mysqli->errno, [2006, 2013], true);
        if($isReconnect && $isNoReConnect) $isReconnect = false;
        if(strlen($this->logPath) > 0) Logs::error('[FALSE]'.($isReconnect?'[ReConnect]':'').'['.$this->Mysqli->errno.']['.$this->Mysqli->error.']->'.$sql, __LINE__, __FILE__, $this->logPath);
        if($isReconnect) try{ if($this->connect()) return $this->query($sql, $isNoLog, true); }catch(MysqlConnectException $MysqlConnectException){}
        return false;
    }

    /**
     * @return int
     * 获取最后插入Id
     */
    public function getId():int
    {
        return is_int($this->Mysqli->insert_id) ? $this->Mysqli->insert_id : (is_string($this->Mysqli->insert_id) ? intval($this->Mysqli->insert_id) : 0);
    }

    /**
     * @return int
     * 获取最后影响行数
     */
    public function getNum():int
    {
        return is_int($this->Mysqli->affected_rows) ? $this->Mysqli->affected_rows : (is_string($this->Mysqli->affected_rows) ? intval($this->Mysqli->affected_rows) : 0);
    }

    /**
     * @param string $sql
     * @param string $name
     * @param bool $isFail
     * @return string
     * 获取查询SQL的某字段结果字符串
     */
    public function getDataString(string $sql, string $name, bool &$isFail = false):string
    {
        $isFail     = false;
        $dataOne    = $this->getDataOne($sql, $isFail);
        return $dataOne[$name] ?? '';
    }

    /**
     * @param string $sql
     * @param string $name
     * @param bool $isFail
     * @return int
     * 获取查询SQL的某字段结果数字
     */
    public function getDataInt(string $sql, string $name, bool &$isFail = false):int
    {
        $isFail     = false;
        $dataOne    = $this->getDataOne($sql, $isFail);
        return intval($dataOne[$name] ?? 0);
    }

    /**
     * @param string $sql
     * @param string $name
     * @param bool $isFail
     * @return float
     * 获取查询SQL的某字段结果浮点数
     */
    public function getDataFloat(string $sql, string $name, bool &$isFail = false):float
    {
        $isFail     = false;
        $dataOne    = $this->getDataOne($sql, $isFail);
        return floatval($dataOne[$name] ?? 0.0);
    }

    /**
     * @param string $sql
     * @param bool $isFail
     * @return array
     * 获取查询多条SQL结果
     */
    public function getData(string $sql, bool &$isFail = false):array
    {
        $resData        = [];
        $sql            = trim($sql);
        if(strlen($sql) < 6){
            $isFail     = true;
            return $resData;
        }
        if(!preg_match('/^(SELECT|SHOW|DESC)\s+/i', $sql)){
            $isFail     = true;
            return $resData;
        }
        if($this->query($sql) === false || !($this->MysqliResult instanceof MysqliResult)){
            $isFail     = true;
            return $resData;
        }
        $isFail     = false;
        while($data = $this->MysqliResult->fetch_array(MYSQLI_ASSOC)){
            if(is_array($data)) $resData[] = $data;
        }
        $this->MysqliResult->free();
        return $resData;
    }

    /**
     * @param string $sql
     * @param bool $isFail
     * @return array
     * 获取查询单条SQL结果
     */
    public function getDataOne(string $sql, bool &$isFail = false):array
    {
        $resData        = [];
        $sql            = trim($sql);
        if(strlen($sql) < 6){
            $isFail     = true;
            return $resData;
        }
        if(!preg_match('/^SELECT|SHOW|DESC\s+/i', $sql)){
            $isFail     = true;
            return $resData;
        }
        if(preg_match('/^SELECT\s+/i', $sql) && !preg_match('/\s+LIMIT\s+\d+/i', $sql)) $sql .= ' LIMIT 1';
        if($this->query($sql) === false || !($this->MysqliResult instanceof MysqliResult)){
            $isFail     = true;
            return $resData;
        }
        $isFail     = false;
        $oneArray   = $this->MysqliResult->fetch_array(MYSQLI_ASSOC);
        $this->MysqliResult->free();
        if(is_array($oneArray)) $resData = $oneArray;
        return $resData;
    }

    /**
     * @param string $sql
     * @param bool $isFail
     * @return int
     * 获取更新、删除、插入影响行数
     */
    public function getDataNum(string $sql, bool &$isFail = false):int
    {
        $sql            = trim($sql);
        if(strlen($sql) < 6){
            $isFail     = true;
            return -1;
        }
        if(!preg_match('/^(UPDATE|DELETE|INSERT)\s+/i', $sql)){
            $isFail     = true;
            return -1;
        }
        if($this->query($sql) === false){
            $isFail     = true;
            return -1;
        }
        $isFail     = false;
        return $this->getNum();
    }

    /**
     * @param string $sql
     * @param bool $isFail
     * @return int
     * 获取插入SQL自增Id
     */
    public function getDataId(string $sql, bool &$isFail = false):int
    {
        $sql            = trim($sql);
        if(strlen($sql) < 6){
            $isFail     = true;
            return -1;
        }
        if(!preg_match('/^INSERT\s+/i', $sql)){
            $isFail     = true;
            return -1;
        }
        if($this->query($sql) === false){
            $isFail     = true;
            return -1;
        }
        $isFail     = false;
        return $this->getId();
    }

    /**
     * @param string $string
     * @return string
     * 过滤安全SQL字符串
     */
    public function getRealEscapeString(string $string):string
    {
        return $this->Mysqli->real_escape_string($string);
    }

    /**
     * @return string
     * 获取MySQL版本字符串
     */
    public function getVersion():string
    {
        $version    = $this->getDataString('SELECT VERSION() AS value', 'value');
        if(preg_match('/^([\d.]+)/', $version, $match)) $version = $match[1];
        return $version;
    }

    /**
     * @return int
     * 获取MySQL当前时间戳
     */
    public function getTime():int
    {
        return $this->getDataInt('SELECT UNIX_TIMESTAMP() AS value', 'value');
    }

    /**
     * @param string $dbName
     * @return array
     * 获取MySQL表名列表
     */
    public function getTableList(string $dbName = ''):array
    {
        $dataArray          = $this->getData('SHOW TABLES FROM '.$this->getDbName($dbName));
        $tableList          = [];
        if(count($dataArray) > 0) foreach($dataArray as $val){
            $tableList[]    = array_shift($val);   // [Tables_in_{dbname}] => tableName
        }
        return $tableList;
    }

    /**
     * @param string $tableName
     * @param string $dbName
     * @return array
     * 获取MySQL表状态
     */
    public function getTableStatus(string $tableName, string $dbName = ''):array
    {
        $tableName  = $this->getTableName($tableName, false);
        $dbName     = $this->getDbName($dbName);
        $sql        = 'SHOW TABLE STATUS FROM '.$dbName.' WHERE NAME=\''.$tableName.'\'';
        // Name Engine Version Row_format Rows Avg_row_length Data_length Max_data_length Index_length Data_free Auto_increment Create_time Update_time Check_time Collation Checksum Create_options Comment
        return $this->getDataOne($sql);
    }

    /**
     * @param string $tableName
     * @param string $dbName
     * @return array
     * 获取MySQL表字段
     */
    public function getTableFieldsList(string $tableName, string $dbName = ''):array
    {
        $dataList       = $this->getData('SHOW FULL FIELDS FROM '.$this->getTableNameAll($tableName, true, '', $dbName));
        // Field Type Collation Null Key Default Extra Privileges Comment
        if(count($dataList) > 0) foreach($dataList as $key => $val){
            unset($val['Privileges']);
            $dataList[$key] = $val;
        }
        return $dataList;
    }

    /**
     * @param string $tableName
     * @param string $dbName
     * @return array
     * 获取MySQL表字段名列表
     */
    public function getTableFieldsNameList(string $tableName, string $dbName = ''):array
    {
        $dataList       = $this->getData('SHOW FULL FIELDS FROM '.$this->getTableNameAll($tableName, true, '', $dbName));
        $dataName       = [];
        if(count($dataList) > 0) foreach($dataList as $val){
            $dataName[] = $val['Field'] ?? array_shift($val);
        }
        return $dataName;
    }

    /**
     * @param string $tableName
     * @param string $tablePrefix
     * @return string
     * 获取表名称(去除前缀和反引号)
     */
    public function getTableNameOnly(string $tableName, string $tablePrefix = ''):string
    {
        $tablePrefixLength      = strlen($tablePrefix);
        if($tablePrefixLength < 1){
            $tablePrefix        = $this->tablePrefix;
            $tablePrefixLength  = strlen($tablePrefix);
        }
        $tableNamePos           = strrpos($tableName, '.');
        if($tableNamePos !== false){
            $tableName          = substr($tableName, $tableNamePos+1);
        }
        $tableName              = trim($tableName, '`');
        if($tablePrefixLength > 0 && strlen($tableName) > $tablePrefixLength && substr($tableName, 0, $tablePrefixLength) === $tablePrefix){
            $tableName          = substr($tableName, $tablePrefixLength);
        }
        return $tableName;
    }

    /**
     * @param string $tableName
     * @param bool $isTrue
     * @param string $tablePrefix
     * @return string
     * 获取表名(自动表前缀)
     */
    public function getTableName(string $tableName, bool $isTrue = true, string $tablePrefix = ''):string
    {
        $tableName  = trim($tableName);
        if(strlen($tableName) < 1) return '';
        if(strlen($tablePrefix) === 0) $tablePrefix = $this->tablePrefix;
        $dotPos     = strrpos($tableName, '.');
        if($dotPos !== false) $tableName = substr($tableName, $dotPos+1);
        $tableName  = preg_replace('/[^a-z\d_]/i', '', $tableName);   // 表名处理
        $tableName  = ($tablePrefix == '' || (substr($tableName, 0, strlen($tablePrefix)) == $tablePrefix)) ? $tableName : $tablePrefix.$tableName;  // 表前缀处理
        return $isTrue ? ('`'.$tableName.'`') : $tableName;
    }

    /**
     * @param string $tableName
     * @param bool $isTrue
     * @param string $tablePrefix
     * @param string $dbName
     * @return string
     * 获取库名和表名(自动表前缀)
     */
    public function getTableNameAll(string $tableName, bool $isTrue = true, string $tablePrefix = '', string $dbName = ''):string
    {
        $tableName  = $this->getTableName($tableName, $isTrue, $tablePrefix);
        $dbName     = $this->getDbName($dbName, $isTrue);
        return $dbName.'.'.$tableName;
    }

    /**
     * @param string $dbName
     * @param bool $isTrue
     * @return string
     * 获取库名
     */
    public function getDbName(string $dbName = '', bool $isTrue = true):string
    {
        $dbName     = trim($dbName);
        strlen($dbName) < 1 && $dbName = $this->serverDbName;
        $dotPos     = strpos($dbName, '.');
        if($dotPos !== false) $dbName = substr($dbName, 0, $dotPos);
        $dbName     = preg_replace('/[^a-z\d_]/i', '', $dbName);   // 库名处理
        return $isTrue ? ('`'.$dbName.'`') : $dbName;
    }

    /**
     * @param bool $isRestart
     * @return bool
     * 开启事务
     */
    public function startTransaction(bool $isRestart = true):bool
    {
        if($this->isTransaction){
            if(!$isRestart) return false;
            if(!$this->commitTransaction()) return false;   // 提交之前的事务
        }
        if($this->isTransaction) return false;
        $this->query('SET AUTOCOMMIT=0');
        $this->query('START TRANSACTION');
        $this->isTransaction = true;
        return true;
    }

    /**
     * @param bool $isEnd
     * @return bool
     * 结束事务
     */
    public function commitTransaction(bool $isEnd = true):bool
    {
        if(!$this->isTransaction) return false;
        $this->query('COMMIT');
        $isEnd && $this->endTransaction();
        return true;
    }

    /**
     * @param bool $isEnd
     * @return bool
     * 回滚事务
     */
    public function backTransaction(bool $isEnd = true):bool
    {
        if(!$this->isTransaction) return false;
        $this->query('ROLLBACK');
        $isEnd && $this->endTransaction();
        return true;
    }

    /**
     * @return bool
     * 截止事务
     */
    private function endTransaction():bool
    {
        if(!$this->isTransaction) return false;
        $this->query('SET AUTOCOMMIT=1');   // 开启自动提交
        $this->isTransaction = false;
        return true;
    }

    /**
     * @return bool
     * 是否已开启事务
     */
    public function isTransaction():bool
    {
        return $this->isTransaction;
    }

    /**
     * @param string $dbName
     * @return array
     * 获取表结构数据
     */
    public function getTableDataList(string $dbName):array
    {
        $tableDataList                  = [];
        $tableList                      = $this->getData('SELECT TABLE_NAME AS tableName, TABLE_COMMENT AS tableComment FROM information_schema.TABLES WHERE TABLE_SCHEMA COLLATE utf8_bin=\''.$this->getRealEscapeString($dbName).'\'');
        // 分表
        $tableGroupList                 = [];
        foreach ($tableList as $tableItem){
            $tableName                  = $tableItem['tableName'];
            if(strlen($tableName) < 1) continue;
            if(preg_match('/^(.*_)tpl$/', $tableName, $match)) $tableGroupList[] = $match[1];   // 识别主分表
        }
        foreach ($tableList as $tableItem){
            $tableName                  = $tableItem['tableName'];
            $tableComment               = $tableItem['tableComment'];
            if(strlen($tableName) < 1) continue;
            if(preg_match('/^(.*_)\d+$/', $tableName, $match) && in_array($match[1], $tableGroupList, true)) continue;  // 跳过分表
            $tableDataList[$tableName]  = ['name' => $tableName, 'comment' => $tableComment, 'columns' => [], 'keys' => []];
        }
        if(count($tableDataList) < 1) return $tableDataList;
        // 表字段
        $columnList                 = $this->getData('SELECT TABLE_NAME AS tableName, COLUMN_NAME AS columnName, COLUMN_COMMENT AS columnComment, ORDINAL_POSITION AS columnSort, COLUMN_DEFAULT AS columnDefault, IS_NULLABLE AS columnNull, COLUMN_TYPE AS columnTypeStr, DATA_TYPE AS columnType, CHARACTER_MAXIMUM_LENGTH AS columnTypeLength, NUMERIC_PRECISION AS columnTypePrecision, NUMERIC_SCALE AS columnTypeScale, COLUMN_KEY AS columnKey, EXTRA AS columnExt FROM information_schema.COLUMNS WHERE TABLE_SCHEMA COLLATE utf8_bin=\''.$this->getRealEscapeString($dbName).'\' ORDER BY tableName ASC, columnSort ASC');
        foreach ($columnList as $columnItem){
            $tableName              = $columnItem['tableName'];
            $columnName             = $columnItem['columnName'];
            $columnComment          = $columnItem['columnComment'];
            $columnSort             = $columnItem['columnSort'];
            $columnDefault          = $columnItem['columnDefault'];
            $columnNull             = $columnItem['columnNull'];
            $columnTypeStr          = $columnItem['columnTypeStr'];
            $columnType             = $columnItem['columnType'];
            $columnTypeLength       = intval($columnItem['columnTypeLength']);
            $columnTypePrecision    = intval($columnItem['columnTypePrecision']);
            $columnTypeScale        = intval($columnItem['columnTypeScale']);
            $columnKey              = strtoupper(trim($columnItem['columnKey']));
            $columnExt              = strtoupper(trim($columnItem['columnExt']));
            if(strlen($columnName) < 1 || strlen($tableName) < 1 || !isset($tableDataList[$tableName])) continue;
            $tableDataList[$tableName]['columns'][$columnName] = [
                'name'              => $columnName,
                'comment'           => $columnComment,
                'sort'              => $columnSort,
                'default'           => $columnDefault,
                'null'              => $columnNull,
                'typeStr'           => $columnTypeStr,
                'type'              => $columnType,
                'typeLength'        => $columnTypeLength,
                'typePrecision'     => $columnTypePrecision,
                'typeScale'         => $columnTypeScale,
                'key'               => strlen($columnKey) > 0 ? [$columnKey] : [],
                'ext'               => $columnExt,
            ];
        }
        // 索引
        $indexList                  = $this->getData('SELECT TABLE_NAME AS tableName, INDEX_NAME AS indexName, INDEX_COMMENT AS indexComment, NON_UNIQUE AS indexNonUnique, GROUP_CONCAT(COLUMN_NAME ORDER BY SEQ_IN_INDEX ASC) AS indexColumns FROM information_schema.STATISTICS WHERE TABLE_SCHEMA COLLATE utf8_bin =\''.$this->getRealEscapeString($dbName).'\' GROUP BY tableName, indexName');
        foreach ($indexList as $indexItem){
            $tableName              = $indexItem['tableName'];
            $indexName              = $indexItem['indexName'];
            $indexComment           = $indexItem['indexComment'];
            $indexNonUnique         = $indexItem['indexNonUnique'];
            $indexColumns           = explode(',', $indexItem['indexColumns']);
            if(strlen($indexName) < 1 || strlen($tableName) < 1 || !isset($tableDataList[$tableName])) continue;
            $tableDataList[$tableName]['keys'][$indexName] = [
                'name'              => $indexName,
                'comment'           => $indexComment,
                'nonUnique'         => $indexNonUnique,
                'columns'           => $indexColumns,
            ];
            if(count($indexColumns) > 0) foreach ($indexColumns as $columnName){
                if(!isset($tableDataList[$tableName]['columns'][$columnName])) continue;
                $columnKeys         = $tableDataList[$tableName]['columns'][$columnName]['key'];
                if(strtolower($indexName) === 'PRIMARY'){
                    if(!in_array('PRI', $columnKeys, true)) $columnKeys[] = 'PRI';
                }else{
                    if($indexNonUnique === 0){
                        if(!in_array('PRI', $columnKeys, true) && !in_array('UNI', $columnKeys, true)) $columnKeys[] = 'UNI';
                    }else{
                        if(!in_array('PRI', $columnKeys, true) && !in_array('MUL', $columnKeys, true)) $columnKeys[] = 'MUL';
                    }
                }
                $tableDataList[$tableName]['columns'][$columnName]['key'] = $columnKeys;
            }
        }
        return $tableDataList;
    }
}